/*
 * IBM Corporation.
 * Copyright (c) 2014 All Rights Reserved.
 */

package com.ibm.iisp.dataie.service;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;

import javax.persistence.EntityManager;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.poifs.filesystem.OfficeXmlFileException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.ibm.iisp.common.exception.I18nAppException;
import com.ibm.iisp.common.util.FileStoreUtils;
import com.ibm.iisp.dataie.model.DataIETable;
import com.ibm.iisp.dataie.model.DataImport;

/**
 * 类作用：
 * @author Johnny@cn.ibm.com
 * 使用说明：
 */
public class ExcelDataImporter {
	Logger log = LoggerFactory.getLogger(getClass());
	private FileStoreUtils fileStoreUtils;
	private EntityManager dao;
	private int sheetIdx = 0;
	private int dataRowNo = 3;
	/**
	 * @param fileStoreUtils
	 * @param dao
	 */
	public ExcelDataImporter(FileStoreUtils fileStoreUtils, EntityManager dao) {
		super();
		this.fileStoreUtils = fileStoreUtils;
		this.dao = dao;
	}

	public Map<String, Object> importDataFile(DataImport dataImport, String tbSuffix) throws I18nAppException {
		HashMap<String, Object> paramMap = new HashMap<>();// 用于返回第一行数据，以便后续过程要取用数据-如DATA_DATE
		boolean wroteSheet = false;// 标记是否有写sheet
		String fn = fileStoreUtils.getAbsFilePath(dataImport.getFileUri());
		dataImport.setInsertCount(0);
		dataImport.setUpdateCount(0);
		dataImport.setDiscardCount(0);
		Workbook wb = null;
		FileInputStream is = null;
		try {
			is = new FileInputStream(fn);
			if (fn.endsWith(".xls")) {
				try {
					wb = new HSSFWorkbook(is);
				} catch (OfficeXmlFileException e) {
					log.info("Imported excel 2010 format with xls ext name. will try to use 2010");
					is.close();
					is = null;
				}
			}
			if (wb == null) {
				if (is == null) {
					is = new FileInputStream(fn);
				}
				wb = new XSSFWorkbook(is);
			}

			log.debug("import file {}...", fn);
			Sheet sheet = getDataSheet(wb);
			try {
				while (sheet != null) {
					log.debug("import sheet at NO. {}...", sheetIdx);
					if (importSheet(dataImport, paramMap, sheet, tbSuffix)) {
						wroteSheet = true;
					}
					sheet = getDataSheet(wb);
				}
			} catch (I18nAppException e) {
				try (OutputStream os = new FileOutputStream(fn)) {
					wb.write(os);
				}
				throw e;
			}
			if (wroteSheet) {
				try (OutputStream os = new FileOutputStream(fn)) {
					wb.write(os);
				}
			}
			wb.close();
		} catch (I18nAppException e1) {
			throw e1;
		} catch (Exception e) {
			log.error("Error to import file", e);
			throw new I18nAppException("IISP_DATA_IE001", new String[] { dataImport.getFileUri() },
				"Error to import file:" + dataImport.getFileUri(), e);

		} finally {
			try {
				is.close();
			} catch (IOException e) {
				log.error("Error to close file.", e);
			}
		}
		return paramMap;
	}

	protected Map<String, ?> params;
	private int colNamRowNo = 1;


	protected boolean importSheet(DataImport dataImport, HashMap<String, Object> paramMap, Sheet sheet, String tbSuffix)
		throws SQLException, I18nAppException {
		params = DataIEUtils.parseParams(sheet, null);
		dataRowNo = 3;
		if (params != null) {
			if (params.get(DataIEUtils._DATA_ROW_START_PARAM) != null) {
				dataRowNo = (Integer) params.get(DataIEUtils._DATA_ROW_START_PARAM);
				dataRowNo--;
			}
			if (params.get(DataIEUtils._COLUMN_ROW_PARAM) != null) {
				colNamRowNo = (Integer) params.get(DataIEUtils._COLUMN_ROW_PARAM);
				colNamRowNo--;
			}
		}
		boolean wroteSheet = false;
		String table = sheet.getRow(0).getCell(0).getStringCellValue().trim();
		String tableTemp = table;
		if (tbSuffix != null) {
			tableTemp = table + tbSuffix;
		}
		DataIETable tb = dao.find(DataIETable.class, table);
		Set<String> requiredColumns = DataIEUtils.getRequiredColumns(tb);
		dataImport.setTableName(tb.getCode());
		dataImport.setTableDesc(tb.getName());
		String autoIdColumn = tb.getAutoIdColumn();
		// 检测一共多少列
		int ttColCount = sheet.getRow(colNamRowNo).getLastCellNum();
		ArrayList<String> allColumns = new ArrayList<>(ttColCount);
		Connection con = dao.unwrap(Connection.class);
		String sql1 = "truncate table " + tableTemp;
		if (tbSuffix != null) {
		Statement st = null;
			try {
				st = con.createStatement();
				log.info(sql1);
				st.executeQuery(sql1);
			} catch (SQLException e) {
				log.debug("Error to truncate table:{}", e.getMessage());
				String sql = "create table " + tableTemp + " as select * from " + table + " where 1 <> 1";
				log.info(sql);
				st.execute(sql);
				// st.execute("alter table " + tableTemp + " add IMP_ID number(19)");
			} finally {
				st.close();
			}
		}
		if (!dataImport.isIncreasement()) {
			// 非增量模式，就是全量模式，需要先清除数据
			dao.createNativeQuery(sql1).executeUpdate();
		} else if (dataImport.getCleanDataClause() != null && dataImport.getCleanDataClause().length() > 0) {
			int cnt = dao.createNativeQuery("delete from " + tableTemp + " where " + dataImport.getCleanDataClause()).executeUpdate();
			log.info("{} rows clean with clause {}.", cnt, dataImport.getCleanDataClause());
		}
		PreparedStatement queryUpd = null;
		PreparedStatement queryInsert = null;
		try {
			String[] keyColumns = null;
			Map<String, Integer> nameParamIdx = null;
			StringBuilder sbSqlUpd = new StringBuilder(" where 1=1");
			String sqlUpdateWhere = null;
			// 如果需要override重复数据，则构造删除query
			if (dataImport.isOverrideDuplication()) {
				nameParamIdx = new HashMap<>();
				String keyColumnsCsv = tb.getKeyColumns();
				if (keyColumnsCsv != null && keyColumnsCsv.length() > 1) {
					keyColumns = keyColumnsCsv.split(",");
					for (int i = 0; i < keyColumns.length; i++) {
						keyColumns[i] = keyColumns[i].trim().toUpperCase();
						nameParamIdx.put(keyColumns[i], i + 1);
						sbSqlUpd.append(" and ").append(keyColumns[i]).append(" = ? ");
					}
				}
				sqlUpdateWhere = sbSqlUpd.toString();
				// log.debug("sbSqlUpd where:{}", sqlUpdateWhere);
				sbSqlUpd.setLength(0);
			}
			StringBuilder sbSqlInsert = new StringBuilder();
			sbSqlInsert.append("insert into ").append(tableTemp).append("(");
			Row row1 = sheet.getRow(colNamRowNo);
			sbSqlUpd.append("update ").append(tableTemp).append(" set ");
			// 设置一个错误消息样式
			Map<String, Object> styles = new HashMap<>();
			styles.put(CellUtil.FILL_BACKGROUND_COLOR, HSSFColorPredefined.RED);
			int colCount = 0;
			int autoIdIdx = -1;
			for (; colCount < ttColCount; colCount++) {
				String colName = row1.getCell(colCount) == null ? null : row1.getCell(colCount).getStringCellValue();
				if (colName == null || colName.length() == 0) {
					break;
				}
				colName = colName.toUpperCase();
				allColumns.add(colName);
				if (colCount > 0) {
					sbSqlInsert.append(",");
				}
				if (sqlUpdateWhere != null && nameParamIdx != null && !nameParamIdx.containsKey(colName)){
					sbSqlUpd.append(colName).append(" = ?,");
				}
				sbSqlInsert.append(colName);
				if (autoIdColumn != null && autoIdColumn.equals(colName)) {
					autoIdIdx = colCount;
				}
			}
			ttColCount = colCount;
			int nonKeyColCount = nameParamIdx == null ? 0 : ttColCount - nameParamIdx.size();

			sbSqlInsert.append(") values (");
			for (int i = 0; i < ttColCount; i++) {
				sbSqlInsert.append("?,");
			}
			sbSqlInsert.deleteCharAt(sbSqlInsert.length() - 1);
			sbSqlInsert.append(")");
			int rowCount = sheet.getLastRowNum() + 1;
			log.debug("sbSqlInsert:{}", sbSqlInsert);
			queryInsert = con.prepareStatement(sbSqlInsert.toString());
			if (sqlUpdateWhere != null) {
				sbSqlUpd.deleteCharAt(sbSqlUpd.length() -1);//删除最后一个逗号
				sbSqlUpd.append(sqlUpdateWhere);
				log.debug("sbSqlUpd:{}", sbSqlUpd);
				queryUpd = con.prepareStatement(sbSqlUpd.toString());
			}
			// 开始分析数据行，从第4行开始
			int insertCount = 0;
			int discardCount = 0;
			int updateCount = 0;
			boolean updated = false;
			for (int i = dataRowNo; i < rowCount; i++) {
				Row dataRow = sheet.getRow(i);
				if (dataRow == null) {
					break;
				}
				int valCount = 1;
				Object val = getCellValue(dataRow.getCell(0));
				// 如果第一列无值就认为后面无数据了
				if (val == null || val.toString().length() == 0) {
					break;
				}
				String errMsg = null;
				for (int j = 0; j < ttColCount; j++) {
					errMsg = null;
					if (j != 0) {
						val = getCellValue(dataRow.getCell(j));
					}
					if (j == autoIdIdx) {
						if (val == null || "".equals(val)) {
							// FIXME: val = dao.getNextId();
						}
					}
					String colName = row1.getCell(j).getStringCellValue().toUpperCase();
					log.trace("Set Value {} = {}", j, val);
					try {
						if ((val == null || "".equals(val)) && requiredColumns.contains(colName)) {
							errMsg = colName + "是必输字段！";
							break;
						} else {
							queryInsert.setObject(j + 1, val);
							// 返回第一行数据，以便后续过程要取用数据-如DATA_DATE
							if (i == dataRowNo) {
								paramMap.put(allColumns.get(j), val);
							}
							if (queryUpd != null) {
								Integer paramIdx = nameParamIdx.get(colName);
								if (paramIdx != null) {
									queryUpd.setObject(nonKeyColCount + paramIdx, val);
								} else {
									queryUpd.setObject(valCount++, val);
								}
							}
						}
					} catch (SQLException e) {
						errMsg = e.getMessage() + "@列:" + (char) (j + 'A');
						throw new I18nAppException("IISP_DATA_IE_DATA_FORMAT_ERR", new String[] { errMsg }, errMsg, null);
					} finally {
						if (errMsg != null) {
							Cell errorCell = dataRow.createCell(ttColCount);
							CellUtil.setCellStyleProperties(errorCell, styles);
							errorCell.setCellValue(errMsg);
							wroteSheet = true;
						}

					}
				}
				if (errMsg == null) {
					try {
						if (queryUpd != null) {
							updated = queryUpd.executeUpdate() > 0;
						}
						if (updated) {
							updateCount++;
						} else {
							queryInsert.executeUpdate();
							insertCount++;
						}
					} catch (SQLException e) {
						discardCount++;
						Cell errorCell = dataRow.createCell(ttColCount);
						CellUtil.setCellStyleProperties(errorCell, styles);
						// errorCell.setCellStyle(cellStyle);
						errorCell.setCellValue(e.getMessage());
						log.warn("Data discarded for table [{}] at row [{}]", tableTemp, i + 1);

					}
				} else {
					discardCount++;
				}
			}
			if (dataImport.getActions() == null) {
				Object dataDate = paramMap.get("DATA_DATE");
				if (dataDate != null) {
					if (dataDate instanceof String) {
						dataImport.setDataDate((String) dataDate);
					} else {

						if (dataDate instanceof Number) {
							int dd = ((Number) dataDate).intValue();
							if (dd > 19000000 && dd <= 99991231) {
								dataImport.setDataDate(String.valueOf(dd));
							}
						}
						if (dataImport.getDataDate() == null) {
							throw new I18nAppException("IISP_DATA_IE_DATA_FORMAT_ERR", new String[] { "DATA_DATE" },
								"DATA_DATE 必须是文本：" + dataDate, null);
						}
					}
				} else {
					Cell cell_1C = sheet.getRow(0).getCell(2);
					if (cell_1C != null) {
						String c1 = (String) getCellValue(cell_1C);
						if (c1 != null) {
							int idx = c1.indexOf("DATA_DATE=");
							if (idx >= 0) {
								String dt = c1.substring(idx + "DATA_DATE=".length());
								idx = dt.indexOf(",");
								if (idx < 0) {
									idx = dt.indexOf("}");
								}
								dt = dt.substring(0, idx);
								log.trace("data date:{}", dt);
								dataImport.setDataDate(dt);
							}
						}
					}
				}
			}
			dataImport.setInsertCount(dataImport.getInsertCount() + insertCount);
			dataImport.setUpdateCount(dataImport.getUpdateCount() + updateCount);
			dataImport.setDiscardCount(dataImport.getDiscardCount() + discardCount);
			if (discardCount > 0) {
				wroteSheet = true;
			}
		} finally {
			if (queryUpd != null) {
				try {
					queryUpd.close();
				} catch (SQLException e) {
					log.error("Failed to close queryDel!", e);
				}
			}
			if (queryInsert != null) {
				try {
					queryInsert.close();
				} catch (SQLException e) {
					log.error("Failed to close queryInsert:", e);
				}
			}
		}
		return wroteSheet;
	}

	private Object getCellValue(Cell cell) {
		if (cell != null) {
			CellType cellType = cell.getCellTypeEnum();
			switch (cellType) {
			case BOOLEAN:
				return cell.getBooleanCellValue();
			case NUMERIC:
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					double d = cell.getNumericCellValue(); 
					Date date = HSSFDateUtil.getJavaDate(d);
					return date;
				}
				return cell.getNumericCellValue();
			case STRING:
				String stringCellValue = cell.getStringCellValue();
				if (stringCellValue != null) {
					stringCellValue = stringCellValue.trim();
					stringCellValue = stringCellValue.replaceAll("^[ *|　*| *| *|\\s*]*", "").replaceAll("[ *|　*| *| *|\\s*]*$", "");
				}
				log.debug("stringCellValue:{}", stringCellValue.charAt(stringCellValue.length() - 1));
				return stringCellValue;
			case FORMULA:
				Object val;
				try {
					/*
					 * 此处判断使用公式生成的字符串有问题，因为HSSFDateUtil.isCellDateFormatted(cell)判断过程中cell
					 * .getNumericCellValue();方法会抛出java.lang.NumberFormatException异常
					 */
					if (HSSFDateUtil.isCellDateFormatted(cell)) {
						val = cell.getDateCellValue();
					} else {
						val = cell.getNumericCellValue();
						if ((Double) val == 0d) {
							val = null;
						}
					}
				} catch (IllegalStateException e) {
					String s = String.valueOf(cell.getRichStringCellValue());
					if (s != null) {
						s = s.trim();
					}
					val = s;
				}
				return val;
			default:
				return cell.toString().trim();
			}
		}

		return null;
	}

	/**
	 * @param book
	 * @return
	 */
	public Sheet getDataSheet(Workbook book) {
		while (sheetIdx < book.getNumberOfSheets()) {
			Sheet s = book.getSheetAt(sheetIdx++);
			if (DataIEUtils.verifySignature(s, null)) {
				return s;
			}
		}
		return null;
	}

}
